At 23:07 +0300 on 19/05/1999, E Westfield wrote:
> Greetings
> Looking for how to build a table with a built in unique sequential numeric
> key (primary optional) and then copy from a flat file to that same field.
> Can not see on the _CREATE TABLE_ nor the _TYPE_ how to do so. Is the OID
> mentioned in the _COPY_ a possible.
>
> I wish to have a new unique ID for any additions to the table without
> haveing to programmatically create one. This is done in other databases so
> I am sure it is available in Postgresql. Thank you for you help in this
> slow learning one.
This question is in the FAQ, ITEM 3.13:
http://www.postgresql.org/docs/faq-english.shtml#3.13
Note that if you want to fill the table from a flat file that doesn't have
the serial numbers, you have to take some steps. Suppose the name of the
serial field is "s1", and the other fields are "f1", "f2"... "fn".
1) Create your table, say, "table1", with the fields s1, f1, f2... fn.
2) Create a temporary table "temp1", with only fields f1, f2... fn.
3) Use COPY to copy from the flat file to "temp1".
4) Use the following insert command to fill "table1":
INSERT INTO table1 (f1, f2, ... fn)
SELECT f1, f2,... fn
FROM temp1;
As you see, there is no mention of the s1 field, and since it has
a default, it fills itself automatically.
5) Drop the temp1 table.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma